const sequelize = require('../config/database')

async function createNotificationTables() {
  try {
    // 创建通知日志表
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS \`notification_logs\` (
        \`id\` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '通知日志ID',
        \`user_id\` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
        \`message_id\` BIGINT UNSIGNED DEFAULT NULL COMMENT '消息ID',
        \`type\` ENUM('wechat_template', 'sms', 'in_app') NOT NULL COMMENT '通知类型',
        \`platform\` ENUM('miniprogram', 'h5', 'both') NOT NULL COMMENT '平台',
        \`template_id\` VARCHAR(100) DEFAULT NULL COMMENT '模板ID',
        \`recipient\` VARCHAR(100) NOT NULL COMMENT '接收者（openid或手机号）',
        \`title\` VARCHAR(100) NOT NULL COMMENT '通知标题',
        \`content\` TEXT NOT NULL COMMENT '通知内容',
        \`template_data\` JSON DEFAULT NULL COMMENT '模板数据',
        \`status\` ENUM('pending', 'sent', 'failed', 'retry') DEFAULT 'pending' COMMENT '发送状态',
        \`error_message\` TEXT DEFAULT NULL COMMENT '错误信息',
        \`retry_count\` INT DEFAULT 0 COMMENT '重试次数',
        \`sent_at\` TIMESTAMP NULL DEFAULT NULL COMMENT '发送时间',
        \`created_at\` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        \`updated_at\` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        PRIMARY KEY (\`id\`),
        KEY \`idx_user_id\` (\`user_id\`),
        KEY \`idx_type\` (\`type\`),
        KEY \`idx_status\` (\`status\`),
        KEY \`idx_created_at\` (\`created_at\`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知日志表';
    `)

    // 检查messages表是否存在，如果不存在则创建
    const [results] = await sequelize.query(`
      SELECT COUNT(*) as count 
      FROM information_schema.tables 
      WHERE table_schema = DATABASE() 
      AND table_name = 'messages'
    `)

    if (results[0].count === 0) {
      await sequelize.query(`
        CREATE TABLE \`messages\` (
          \`id\` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '消息ID',
          \`user_id\` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
          \`type\` ENUM('system', 'consume', 'reward', 'expire', 'activity') NOT NULL COMMENT '消息类型',
          \`title\` VARCHAR(100) NOT NULL COMMENT '消息标题',
          \`content\` TEXT NOT NULL COMMENT '消息内容',
          \`extra_data\` JSON DEFAULT NULL COMMENT '额外数据',
          \`is_read\` TINYINT DEFAULT 0 COMMENT '是否已读',
          \`read_time\` TIMESTAMP NULL DEFAULT NULL COMMENT '阅读时间',
          \`created_at\` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
          PRIMARY KEY (\`id\`),
          KEY \`idx_user_id\` (\`user_id\`),
          KEY \`idx_type\` (\`type\`),
          KEY \`idx_is_read\` (\`is_read\`),
          KEY \`idx_created_at\` (\`created_at\`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息表';
      `)
      console.log('Messages table created successfully')
    } else {
      console.log('Messages table already exists')
    }

    console.log('Notification tables created successfully')
  } catch (error) {
    console.error('Error creating notification tables:', error)
    throw error
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  createNotificationTables()
    .then(() => {
      console.log('Database setup completed')
      process.exit(0)
    })
    .catch((error) => {
      console.error('Database setup failed:', error)
      process.exit(1)
    })
}

module.exports = createNotificationTables